﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ARR.Entities.Gateways;
using System.Data;
using DDF.Data.Providers;
using DDF.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace ARR.Model.Repositories
{
    public static class ReportsRepository
    {
        private static string connString = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;

        public static List<StoreCoveragePenetrationRow> GetStoreCoveragePenetration(int rangeReviewId, int categoryId)
        {
            List<StoreCoveragePenetrationRow> rows = new List<StoreCoveragePenetrationRow>();

            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;

                database.NewCommand("Report_StoreCoverage_Penetration", CommandType.StoredProcedure);
                database.AddInParameter("@RangeReviewId", DbType.Int32, rangeReviewId);
                database.AddInParameter("@CategoryId", DbType.Int16, categoryId);

                IDataReader reader = database.ExecuteReader();

                Load(reader, rows);
            }

            return rows;
        }

        public static List<SalesCategoryComparisonRow> GetSalesCategoryComparison(int rangeReviewId, int? categoryId, int? storeId, int? storeGroupId, bool? delist = null)
        {
            List<SalesCategoryComparisonRow> sales = new List<SalesCategoryComparisonRow>();
            
            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;

                database.NewCommand("Report_CategorySameStoreComparison", CommandType.StoredProcedure);
                database.AddInParameter("@RangeReviewId", DbType.Int32, rangeReviewId);
                database.AddInParameter("@CategoryId", DbType.Int32, categoryId ?? 0);
                database.AddInParameter("@StoreId", DbType.Int32, storeId ?? 0);                
                database.AddInParameter("@StoreGroupId", DbType.Int32, storeGroupId ?? 0);
                database.AddInParameter("@Delist", DbType.Boolean, delist);
                

                IDataReader reader = database.ExecuteReader();

                Load(reader, sales);
            }     
            
            return sales;
        }

        public static List<SalesCategoryComparisonRow> GetCategorySameStoreComparisonCombos(int rangeReviewId, int categoryId, byte exchangeRateType)
        {
            List<SalesCategoryComparisonRow> sales = new List<SalesCategoryComparisonRow>();

            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;

                database.NewCommand("Report_CategorySameStoreComparison_Combos", CommandType.StoredProcedure);
                database.AddInParameter("@RangeReviewId", DbType.Int32, rangeReviewId);
                database.AddInParameter("@CategoryId", DbType.Int32, categoryId);
                database.AddInParameter("@ExchangeRate", DbType.Int32, exchangeRateType);
                IDataReader reader = database.ExecuteReader();

                Load(reader, sales);
            }

            return sales;
        }

        public static List<SalesCategoryComparisonRow> GetSalesCategoryComparisonHistoryBenchmark(int rangeReviewId, int categoryId, int historyGroupId, bool isPriorYear, bool totalStore)
        {
            List<SalesCategoryComparisonRow> sales = new List<SalesCategoryComparisonRow>();

            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;

                database.NewCommand("Report_CategorySameStoreComparisonHistoryBenchmark", CommandType.StoredProcedure);
                database.AddInParameter("@RangeReviewId", DbType.Int32, rangeReviewId);
                database.AddInParameter("@CategoryId", DbType.Int32, categoryId);
                database.AddInParameter("@HistoryGroupId", DbType.Int32, historyGroupId);
                database.AddInParameter("@IsPriorYear", DbType.Boolean, isPriorYear);
                database.AddInParameter("@TotalStore", DbType.Boolean, totalStore);

                IDataReader reader = database.ExecuteReader();

                Load(reader, sales);
            }

            return sales;
        }

        public static List<SalesCategoryComparisonParetoRow> GetSalesCategoryComparisonPareto(int rangeReviewId, int? categoryId, bool? includePriorYear, int attributeTypeId, byte exchangeRateType)
        {
            List<SalesCategoryComparisonParetoRow> sales = new List<SalesCategoryComparisonParetoRow>();

            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;

                database.NewCommand("Report_CategorySameStoreComparison_Pareto", CommandType.StoredProcedure);
                database.AddInParameter("@RangeReviewId", DbType.Int32, rangeReviewId);
                database.AddInParameter("@CategoryId", DbType.Int32, categoryId ?? 0);
                database.AddInParameter("@IncludePriorYear", DbType.Boolean, includePriorYear);
                database.AddInParameter("@AttributeTypeId", DbType.Int32, attributeTypeId);
                database.AddInParameter("@ExchangeRate", DbType.Int32, exchangeRateType);
                IDataReader reader = database.ExecuteReader();

                Load(reader, sales);
            }            

            return sales;
        }

        public static List<SalesCategoryComparisonByStoreRow> GetSalesCategoryComparisonByStore(int rangeReviewId, int? categoryId, byte exchangeRateType)
        {
            List<SalesCategoryComparisonByStoreRow> sales = new List<SalesCategoryComparisonByStoreRow>();


            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;

                database.NewCommand("Report_CategoryComparisonByStores", CommandType.StoredProcedure);
                database.AddInParameter("@RangeReviewId", DbType.Int32, rangeReviewId);
                database.AddInParameter("@CategoryId", DbType.Int32, categoryId ?? 0);
                database.AddInParameter("@ExchangeRate", DbType.Int32, exchangeRateType);

                IDataReader reader = database.ExecuteReader();

                Load(reader, sales);
            }

            return sales;
        }

        public static List<SalesCategoryComparisonByDateRow> GetSalesCategoryComparisonByDate(int rangeReviewId, int? categoryId, byte exchangeRateType)
        {
            List<SalesCategoryComparisonByDateRow> sales = new List<SalesCategoryComparisonByDateRow>();
            
            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 300;

                database.NewCommand("Report_CategoryComparisonByDate", CommandType.StoredProcedure);
                database.AddInParameter("@RangeReviewId", DbType.Int32, rangeReviewId);
                database.AddInParameter("@CategoryId", DbType.Int32, categoryId ?? 0);
                database.AddInParameter("@ExchangeRate", DbType.Int32, exchangeRateType);

                IDataReader reader = database.ExecuteReader();

                Load(reader, sales);
            }

            return sales;
        }

        public static List<SalesCategoryComparisonBySpaceRow> GetSalesCategoryComparisonBySpace(int rangeReviewId, int categoryId, bool showPlanogramTypes, byte exchangeRateType)
        {
            List<SalesCategoryComparisonBySpaceRow> sales = new List<SalesCategoryComparisonBySpaceRow>();

            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 900;

                database.NewCommand("Report_CategorySameStoreComparisonBySpace", CommandType.StoredProcedure);
                database.AddInParameter("@RangeReviewId", DbType.Int32, rangeReviewId);
                database.AddInParameter("@CategoryId", DbType.Int32, categoryId);
                database.AddInParameter("@ShowPlanogramTypes", DbType.Boolean, showPlanogramTypes);
                database.AddInParameter("@ExchangeRate", DbType.Int32, exchangeRateType);


                IDataReader reader = database.ExecuteReader();

                Load(reader, sales);
            }

            return sales;
        }

        public static List<ArticleInventoryAndSalesRow> GetInventoryOfAnalyzedArticles(int rangeReviewId, byte exchangeRateType)
        {
            List<ArticleInventoryAndSalesRow> items = new List<ArticleInventoryAndSalesRow>();

            using (Database database = new SqlDataProvider(connString, "{0}Cursor", "dbo"))
            {
                database.Timeout = 300;

                database.NewCommand("Report_InventoryOfAnalyzedArticles", CommandType.StoredProcedure);
                database.AddInParameter("@RangeReviewId", DbType.Int32, rangeReviewId);
                database.AddInParameter("@ExchangeRate", DbType.Int32, exchangeRateType);

                IDataReader reader = database.ExecuteReader();

                Load(reader, items);
            }

            return items;
        }

        private static void Load(IDataReader reader, List<StoreCoveragePenetrationRow> items)
        {
            StoreCoveragePenetrationRow row;

            while (reader.Read())
            {
                row = new StoreCoveragePenetrationRow()
                {
                    StoreId = Util.CastDbValue<short>(reader["StoreId"]),
                    ArticleId = Util.CastDbValue<int>(reader["ArticleId"]),
                    SKUCoverage = Util.CastDbValue<bool>(reader["SKUCoverage"]),
                    SKUPenetration = Util.CastDbValue<bool>(reader["SKUPenetration"]),
                    Sales = Util.CastDbValue<decimal>(reader["Sales"]),
                    StoreSegmentation1 = Util.CastDbValue<string>(reader["StoreSegmentation1"]),
                    StoreSegmentation2 = Util.CastDbValue<string>(reader["StoreSegmentation2"]),
                    Region = Util.CastDbValue<string>(reader["Region"]),
                    Subregion = Util.CastDbValue<string>(reader["Subregion"]),
                    City = Util.CastDbValue<string>(reader["City"]),
                    State = Util.CastDbValue<string>(reader["State"]),
                    ShoppingOccasion = Util.CastDbValue<string>(reader["ShoppingOccasion"]),
                    Size = Util.CastDbValue<string>(reader["Size"]),
                    Format = Util.CastDbValue<string>(reader["Format"]),
                    ShopType = Util.CastDbValue<string>(reader["ShopType"]),
                    Layout = Util.CastDbValue<string>(reader["Layout"]),
                    OpeningHours = Util.CastDbValue<string>(reader["OpeningHours"]),
                    SELCustomers = Util.CastDbValue<string>(reader["SELCustomers"]),
                    SELLocation = Util.CastDbValue<string>(reader["SELLocation"]),
                    SEL = Util.CastDbValue<string>(reader["SEL"]),
                    Attribute1 = Util.CastDbValue<string>(reader["Attribute1"]),
                    Attribute2 = Util.CastDbValue<string>(reader["Attribute2"]),
                    TotalStoresCoverage = Util.CastDbValue<int>(reader["TotalStoresCoverage"])
                };

                items.Add(row);
            }

            reader.Close();
        }

        private static void Load(IDataReader reader, List<ArticleInventoryAndSalesRow> items)
        {
            ArticleInventoryAndSalesRow row;

            while (reader.Read())
            {
                row = new ArticleInventoryAndSalesRow()
                {
                    Id = Util.CastDbValue<int>(reader["Id"]),
                    Code = Util.CastDbValue<string>(reader["Code"]),
                    Name = Util.CastDbValue<string>(reader["Name"]),
                    Category = Util.CastDbValue<string>(reader["Category"]),
                    Delist = Util.CastDbValue<bool>(reader["Delist"]),
                    IsNew = Util.CastDbValue<bool>(reader["IsNew"]),
                    IsDelisted = Util.CastDbValue<bool>(reader["IsDelisted"]),
                    IsDeletedRangeReview = Util.CastDbValue<bool>(reader["IsDeletedRangeReview"]),
                    InventoryCost = Util.CastDbValue<decimal>(reader["InventoryCost"])
                };

                items.Add(row);
            }

            reader.Close();
        }

        private static void Load(IDataReader reader, List<SalesCategoryComparisonBySpaceRow> sales)
        {
            SalesCategoryComparisonBySpaceRow row;

            while (reader.Read())
            {
                row = new SalesCategoryComparisonBySpaceRow()
                {
                    Id = Util.CastDbValue<int>(reader["Id"]),
                    Code = Util.CastDbValue<string>(reader["Code"]),
                    Name = Util.CastDbValue<string>(reader["Name"]),
                    Category = Util.CastDbValue<string>(reader["Category"]),
                    Delist = Util.CastDbValue<bool>(reader["Delist"]),
                    Units = Util.CastDbValue<decimal>(reader["Units"]),
                    Sales = Util.CastDbValue<decimal>(reader["Sales"]),
                    GM = Util.CastDbValue<decimal>(reader["GM"]),
                    Width = Util.CastDbValue<decimal>(reader["Width"]),
                    Facings = Util.CastDbValue<decimal>(reader["Facings"]),
                    LinearSpace = Util.CastDbValue<decimal>(reader["LinearSpace"]),
                    Location1 = Util.CastDbValue<string>(reader["Location1"]),
                    PlanogramTypeId = Util.CastDbValue<int?>(reader["PlanogramType"]),
                    InventoryCost = Util.CastDbValue<decimal>(reader["InventoryCost"]),
                    Planograms = Util.CastDbValue<string>(reader["Planograms"]),
                    IsDeletedRangeReview = Util.CastDbValue<bool>(reader["IsDeletedRangeReview"]),
                    Brand = Util.CastDbValue<string>(reader["Brand"])
                };

                sales.Add(row);
            }

            reader.Close();
        }

        private static void Load(IDataReader reader, List<SalesCategoryComparisonByDateRow> sales)
        {
            SalesCategoryComparisonByDateRow row;

            while (reader.Read())
            {
                row = new SalesCategoryComparisonByDateRow()
                {
                    Id = Util.CastDbValue<int>(reader["Id"]),
                    Code = Util.CastDbValue<string>(reader["Code"]),
                    Name = Util.CastDbValue<string>(reader["Name"]),
                    Brand = Util.CastDbValue<string>(reader["Brand"]),
                    Category = Util.CastDbValue<string>(reader["Category"]),
                    Subcategory = Util.CastDbValue<string>(reader["Subcategory"]),
                    MC = Util.CastDbValue<string>(reader["MC"]),
                    Delist = Util.CastDbValue<bool>(reader["Delist"]),
                    IsDelisted = Util.CastDbValue<bool>(reader["IsDelisted"]),
                    IsDeletedRangeReview = Util.CastDbValue<bool>(reader["IsDeletedRangeReview"]),
                    IsNew = Util.CastDbValue<bool>(reader["IsNew"]),
                    Units = Util.CastDbValue<int>(reader["Units"]),
                    Sales = Util.CastDbValue<decimal>(reader["Sales"]),
                    GM = Util.CastDbValue<decimal>(reader["GM"]),
                    Date = Util.CastDbValue<DateTime>(reader["Date"]),
                    IsPriorYear = Util.CastDbValue<bool>(reader["IsPriorYear"])
                };

                sales.Add(row);
            }

            reader.Close();
        }

        private static void Load(IDataReader reader, List<SalesCategoryComparisonByStoreRow> sales)
        {
            SalesCategoryComparisonByStoreRow row;

            while (reader.Read())
            {
                row = new SalesCategoryComparisonByStoreRow()
                {
                    StoreId = Util.CastDbValue<int>(reader["Id"]),
					StoreCode = Util.CastDbValue<string>(reader["Code"]),
					StoreName = Util.CastDbValue<string>(reader["Name"]),
					Region = Util.CastDbValue<string>(reader["Region"]),
					Subregion = Util.CastDbValue<string>(reader["Subregion"]),
					City = Util.CastDbValue<string>(reader["City"]),
					State = Util.CastDbValue<string>(reader["State"]),
					SEL = Util.CastDbValue<string>(reader["SEL"]),
					ShoppingOccasion = Util.CastDbValue<string>(reader["ShoppingOccasion"]),
					Size = Util.CastDbValue<string>(reader["Size"]),
					Address = Util.CastDbValue<string>(reader["Address"]),
					Phone = Util.CastDbValue<string>(reader["Phone"]),
					Fax = Util.CastDbValue<string>(reader["Fax"]),
					Format = Util.CastDbValue<string>(reader["Format"]),
					PBL = Util.CastDbValue<string>(reader["PBL"]),
					Mail = Util.CastDbValue<string>(reader["Mail"]),
					Contact = Util.CastDbValue<string>(reader["Contact"]),
					SiteLocation = Util.CastDbValue<string>(reader["SiteLocation"]),
					ShopType = Util.CastDbValue<string>(reader["ShopType"]),
					Layout = Util.CastDbValue<string>(reader["Layout"]),
					OpeningHours = Util.CastDbValue<string>(reader["OpeningHours"]),
					Attribute1 = Util.CastDbValue<string>(reader["Attribute1"]),
					Attribute2 = Util.CastDbValue<string>(reader["Attribute2"]),
                    Units = Util.CastDbValue<decimal>(reader["Units"]),
                    Sales = Util.CastDbValue<decimal>(reader["Sales"]),
                    GM = Util.CastDbValue<decimal>(reader["GM"]),
                    Category = Util.CastDbValue<string>(reader["Category"]),
                    InventoryCost = Util.CastDbValue<decimal>(reader["InventoryCost"]),
                };

                sales.Add(row);
            }

            reader.Close();
        }

        private static void Load(IDataReader reader, List<SalesCategoryComparisonParetoRow> sales)
        {
            SalesCategoryComparisonParetoRow row;

            while (reader.Read())
            {
                row = new SalesCategoryComparisonParetoRow()
                {
                    Id = Util.CastDbValue<int>(reader["Id"]),
                    Code = Util.CastDbValue<string>(reader["Code"]),
                    Name = Util.CastDbValue<string>(reader["Name"]),
                    Brand = Util.CastDbValue<string>(reader["Brand"]),
                    Manufacturer = Util.CastDbValue<string>(reader["Manufacturer"]),
                    UnitsInCombos = Util.CastDbValue<decimal>(reader["UnitsInCombos"]),
                    Units = Util.CastDbValue<decimal>(reader["Units"]),
                    Sales = Util.CastDbValue<decimal>(reader["Sales"]),
                    GM = Util.CastDbValue<decimal>(reader["GM"]),
                    Coverage = Util.CastDbValue<decimal>(reader["Coverage"]),
                    StoreCoverage = Util.CastDbValue<decimal>(reader["StoreCoverage"]),
                    StoreCoverageQuantity = Util.CastDbValue<int>(reader["StoreCoverageQuantity"]),
                    MonthsCoverage = Util.CastDbValue<decimal>(reader["MonthsCoverage"]),
                    MonthsCoverageQuantity = Util.CastDbValue<int>(reader["MonthsCoverageQuantity"]),
                    Category = Util.CastDbValue<string>(reader["Category"]),
                    Subcategory = Util.CastDbValue<string>(reader["Subcategory"]),
                    MC = Util.CastDbValue<string>(reader["MC"]),
                    Attribute1 = Util.CastDbValue<string>(reader["Attribute1"]),
                    Attribute2 = Util.CastDbValue<string>(reader["Attribute2"]),
                    Attribute3 = Util.CastDbValue<string>(reader["Attribute3"]),
                    Attribute4 = Util.CastDbValue<string>(reader["Attribute4"]),
                    Attribute5 = Util.CastDbValue<string>(reader["Attribute5"]),
                    Delist = Util.CastDbValue<bool>(reader["Delist"]),
                    DelistComment = Util.CastDbValue<string>(reader["DelistComment"]),
                    IsDeletedRangeReview = Util.CastDbValue<bool>(reader["IsDeletedRangeReview"]),
                    IsDeletedPerformance = Util.CastDbValue<bool>(reader["IsDeletedPerformance"]),
                    IsDelisted = Util.CastDbValue<bool>(reader["IsDelisted"]),
                    IsNew = Util.CastDbValue<bool>(reader["IsNew"]),
                    InventoryCost = Util.CastDbValue<decimal>(reader["InventoryCost"]),
                    Width = Util.CastDbValue<decimal>(reader["Width"]),
                    Facings = Util.CastDbValue<decimal>(reader["Facings"]),
                    IsPriorYear = Util.CastDbValue<bool>(reader["IsPriorYear"]),
                    TheoreticalMargin = Util.CastDbValue<decimal>(reader["TheoreticalMargin"]),
                    Segment = Util.CastDbValue<string>(reader["Segment"]),
                    PIDiff = Util.CastDbValue<decimal>(reader["PIDiff"]),
                    ChargeOffs = Util.CastDbValue<decimal>(reader["ChargeOffs"]),
                    Rebates = Util.CastDbValue<decimal>(reader["Rebates"]),
                    CategoryId = Util.CastDbValue<short>(reader["CategoryId"]),
                    SubcategoryId = Util.CastDbValue<short>(reader["SubcategoryId"])
                };

                sales.Add(row);
            }

            reader.Close();
        }

        private static void Load(IDataReader reader, List<SalesCategoryComparisonRow> sales)
        {
            SalesCategoryComparisonRow row;

            while (reader.Read())
            {
                row = new SalesCategoryComparisonRow()
                {
                    Name = Util.CastDbValue<string>(reader["Name"]),
                    Title = Util.CastDbValue<string>(reader["Title"]),
                    CategoryId = Util.CastDbValue<short>(reader["CategoryId"]),
                    Category = Util.CastDbValue<string>(reader["Category"]),
                    Units = Util.CastDbValue<decimal>(reader["Units"]),
                    Sales = Util.CastDbValue<decimal>(reader["Sales"]),
                    GM = Util.CastDbValue<decimal>(reader["GM"]),
                    UnitsShare = Util.CastDbValue<decimal>(reader["UnitsShare"]),
                    SalesShare = Util.CastDbValue<decimal>(reader["SalesShare"]),
                    GMShare = Util.CastDbValue<decimal>(reader["GMShare"]),
                    PIDiff = Util.CastDbValue<decimal>(reader["PIDiff"]),
                    ChargeOffs = Util.CastDbValue<decimal>(reader["ChargeOffs"]),
                    InventoryCost = Util.CastDbValue<decimal>(reader["InventoryCost"]),
                    LinearSpace = Util.CastDbValue<decimal>(reader["LinearSpace"]),
                    SKUs = Util.CastDbValue<int>(reader["SKUs"]),
                    Sales80 = Util.CastDbValue<int>(reader["Sales80"]),
                    IsPriorYear = Util.CastDbValue<bool>(reader["IsPriorYear"])
                };

                sales.Add(row);
            }

            reader.Close();
        }
    }
}
